package com.gjj.demo.poi.excel;

import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

/**
 * 创建新的工作薄
 * 创建新的sheet页
 * 创建行
 * 创建单元格
 * <p/>
 * <p/>
 * Created by mggwc on 2016/6/5.
 */
public class MainTest {
    /**
     * 1
     * 2
     * 3.
     */
    public static void main(String[] args) throws Exception {

        //创建工作薄和单元格，初始化数据
//        createCell();
        //遍历一个工作薄
//        displaySheet();
        //抽取文本
        extractInner();
    }

    //抽取文本
    private static void extractInner() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("e:\\poi.xls");
        POIFSFileSystem fileSystem = new POIFSFileSystem(fileInputStream); //流转poi
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); //创建工作薄
        ExcelExtractor excelExtractor = new ExcelExtractor(workbook);
        excelExtractor.setIncludeSheetNames(false); //不需要sheet名字
        System.out.println(excelExtractor.getText());
    }

    /*
    * 获取excel中所有内容
    * */
    private static void displaySheet() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("e:\\poi.xls");
        POIFSFileSystem fileSystem = new POIFSFileSystem(fileInputStream); //流转poi
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); //创建工作薄
        HSSFSheet sheet = workbook.getSheetAt(0);  //拿到第一个sheet
        if (null == sheet) {
            return;
        }
        //便利行row
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            //便利列cell
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                String rs = getValue(cell);
                System.out.println(" " + rs);
            }

        }
    }

    /*
    * 判断类型
    * */
    private static String getValue(HSSFCell hssfCell) {

        if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { //是布尔类型？
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { //是string类型？
            return hssfCell.getStringCellValue();
        } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { //是数字类型？
            return String.valueOf(hssfCell.getNumericCellValue());
        } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { //是时间类型？
            return String.valueOf(hssfCell.getCellFormula());
        } else if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {//是时间类型？
            return String.valueOf(hssfCell.getDateCellValue());
        }
        return null;
    }


    public static void createCell() throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook(); //工作薄
        FileOutputStream fileOutputStream = new FileOutputStream("e:\\poi.xls");
        HSSFSheet sheet1 = workbook.createSheet("first");  //页

        HSSFRow row1 = sheet1.createRow(0);  //行，第1行从0开始
        Cell cell11 = row1.createCell(0);//第一行第一列
        Cell cell12 = row1.createCell(1);//第一行第二列
        Cell cell13 = row1.createCell(2);//第一行第三列
        Cell cell14 = row1.createCell(3);//第一行第三列

        cell11.setCellValue("张三");
        cell12.setCellValue(Calendar.getInstance()); //后续设置时间显示格式
        cell13.setCellValue("男");
        cell14.setCellValue(HSSFCell.CELL_TYPE_NUMERIC);

        //一个处理格式样式的工具类
        CreationHelper creationHelper = workbook.getCreationHelper();
        //设置单元格的格式Date
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        cell12.setCellStyle(cellStyle); //给时间列设置格式
        workbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
    }


}
